package org.chen.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.chen.dto.MovieDTO;
import org.chen.enu.ContentPosition;
import org.chen.exception.ServiceException;
import org.chen.mapper.MovieMapper;
import org.chen.mapper.MovieUploadBatchMapper;
import org.chen.model.MovieJson;
import org.chen.model.MovieUploadBatch;
import org.chen.response.BatchProgressResponse;
import org.chen.service.IMovieJsonService;
import org.chen.template.MovieDetail;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.task.AsyncTaskExecutor;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

@Service
@Slf4j
public class MovieService extends ServiceImpl<MovieMapper, MovieJson> implements IMovieJsonService {
    
    @Autowired
    private RedisTemplate<String, String> redisTemplate;
    
    @Autowired
    private MovieJsonGenerator jsonGenerator;

    @Autowired
    private ObjectMapper objectMapper;

    @Qualifier("movieTaskExecutor")
    @Autowired
    private AsyncTaskExecutor taskExecutor;

    @Autowired
    private MovieUploadBatchMapper uploadBatchMapper;

    // 注入事务管理器
    @Autowired
    private TransactionTemplate transactionTemplate;
    
    private static final String REDIS_KEY_PREFIX = "movie:json:";
    private static final long REDIS_EXPIRE_TIME = 24; // 24小时过期
    private static final String REDIS_PAGE_KEY = "movie:page:";
    private static final long PAGE_EXPIRE_TIME = 30; // 30分钟过期

    public void processExcelAsync(MultipartFile file, ContentPosition contentPosition, String batchNo) {
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);

            int successCount = 0;
            int failCount = 0;

            // 验证表头
            Row headerRow = sheet.getRow(0);
            validateExcelHeader(headerRow);

            // 分批处理数据
            int batchSize = 100;  // 每批处理的数量
            List<MovieDTO> batchItems = new ArrayList<>();

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (isEmptyRow(row)) continue;

                try {
                    MovieDTO movieDTO = processRow(row, contentPosition);
                    batchItems.add(movieDTO);

                    // 达到批处理大小或最后一行时处理
                    if (batchItems.size() >= batchSize || i == sheet.getLastRowNum()) {
                        processBatch(batchItems);
                        successCount += batchItems.size();
                        updateBatchProgress(batchNo, successCount, failCount);
                        batchItems.clear();
                    }
                } catch (Exception e) {
                    log.error("处理第{"+i+"}行失败: {"+e.getMessage()+"}");
                    failCount++;
                    updateBatchProgress(batchNo, successCount, failCount);
                }
            }

            // 更新最终状态
            completeBatch(batchNo, successCount, failCount);

        } catch (Exception e) {
            log.error("批处理失败", e);
            failBatch(batchNo, e.getMessage());
        }
    }

    private void validateExcelFile(MultipartFile file) throws ServiceException {
        if (file == null || file.isEmpty()) {
            throw new ServiceException("请选择要上传的Excel文件");
        }
        String fileName = file.getOriginalFilename();
        if (fileName == null || (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls"))) {
            throw new ServiceException("请上传正确的Excel文件格式(.xlsx或.xls)");
        }
    }

    private void validateExcelHeader(Row headerRow) throws ServiceException {
        if (headerRow == null ||
                headerRow.getCell(0) == null ||
                !headerRow.getCell(0).getStringCellValue().equals("name")) {
            throw new ServiceException("Excel表头格式不正确，需要包含 'name' 列");
        }

        if (headerRow.getCell(1) == null ||
                !headerRow.getCell(1).getStringCellValue().equals("slug")) {
            throw new ServiceException("Excel表头格式不正确，需要包含 'slug' 列");
        }

        // content 列是可选的，如果存在则必须是第二列
        if (headerRow.getCell(2) != null &&
                !headerRow.getCell(2).getStringCellValue().equals("content")) {
            throw new ServiceException("Excel表头格式不正确，第二列需要是 'content' 列");
        }
    }

    private boolean isEmptyRow(Row row) {
        if (row == null) return true;
        Cell nameCell = row.getCell(0);
        return nameCell == null || nameCell.getStringCellValue().trim().isEmpty();
    }

    private MovieDTO processRow(Row row, ContentPosition contentPosition) throws ServiceException {
        String name = getCellValueAsString(row.getCell(0));
        String slug = getCellValueAsString(row.getCell(1));
        String content = getCellValueAsString(row.getCell(2));  // 可能为空

        if (name.trim().isEmpty()) {
            throw new ServiceException("name不能为空");
        }

        MovieDTO movieDTO = new MovieDTO();
        movieDTO.setSlug(generateSlug(slug));
        movieDTO.setName(name);
        movieDTO.setContent(content.trim().isEmpty() ? null : content); // 如果content为空，设为null
        movieDTO.setContentPosition(contentPosition);

        return movieDTO;
    }

    private String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> String.valueOf(cell.getNumericCellValue());
            default -> "";
        };
    }

    public void saveToRedisAndDB(MovieDTO movieDTO, String json) throws ServiceException {
        try {
            // 从JSON中提取电影名称作为唯一标识
            String movieSlug = movieDTO.getSlug();
            // 保存到Redis
            saveToRedis(movieSlug, json);

            clearPageCache();
            // 使用TransactionTemplate进行事务操作
            transactionTemplate.execute(status -> {
                try {
                    // 保存到数据库
                    MovieJson entity = new MovieJson();
                    entity.setName(movieDTO.getName());
                    entity.setSlug(movieDTO.getSlug());
                    entity.setJsonContent(json);
                    entity.setCreateTime(LocalDateTime.now());
                    entity.setUpdateTime(LocalDateTime.now());

                    // 先查询是否存在
                    MovieJson existingEntity = this.baseMapper.selectOne(
                            new LambdaQueryWrapper<MovieJson>()
                                    .eq(MovieJson::getSlug, movieDTO.getSlug())
                    );

                    if (existingEntity != null) {
                        entity.setId(existingEntity.getId());
                        this.baseMapper.updateById(entity);
                    } else {
                        this.baseMapper.insert(entity);
                    }

                    return Boolean.TRUE;
                } catch (Exception e) {
                    log.error("数据库操作失败", e);
                    status.setRollbackOnly();
                    throw new ServiceException("数据库操作失败: " + e.getMessage());
                }
            });
        } catch (Exception e) {
            log.error("保存数据失败", e);
            // 如果数据库操作失败，删除Redis中的数据
            redisTemplate.delete(REDIS_KEY_PREFIX + movieDTO.getSlug());
            throw new ServiceException("保存数据失败: " + e.getMessage());
        }
    }

    private void saveToRedis(String slug, String json) {
        try {
            redisTemplate.opsForValue().set(
                    REDIS_KEY_PREFIX + slug,
                    json,
                    REDIS_EXPIRE_TIME,
                    TimeUnit.HOURS
            );
        } catch (Exception e) {
            log.error("Redis操作失败", e);
            throw new ServiceException("Redis操作失败: " + e.getMessage());
        }
    }

    private String generateSlug(String slug) {
        return slug.toLowerCase()
                .replaceAll("[^a-z0-9\\s-]", "")
                .replaceAll("\\s+", "-")
                .replaceAll("-+", "-")
                .trim();
    }

    @Override
    public IPage<MovieJson> getMovieListPage(Page<MovieJson> page, String slug) {
        String cacheKey = generatePageCacheKey(page.getCurrent(), page.getSize(), slug);

        // 尝试从Redis获取
        String cachedData = redisTemplate.opsForValue().get(cacheKey);
        if (cachedData != null) {
            try {
                return objectMapper.readValue(cachedData,
                        new TypeReference<Page<MovieJson>>() {});
            } catch (Exception e) {
                log.error("Redis数据解析失败", e);
            }
        }

        // 从数据库查询
        LambdaQueryWrapper<MovieJson> wrapper = new LambdaQueryWrapper<>();
        if (StringUtils.hasText(slug)) {
            wrapper.like(MovieJson::getSlug, slug);
        }

        // 手动查询总数
        long total = baseMapper.selectCount(wrapper);

        // 设置真实的总数
        page.setTotal(total);

        // 查询当前页数据
        IPage<MovieJson> moviePage = this.page(page, wrapper);
        moviePage.setTotal(total);  // 确保设置正确的总数

        // 存入Redis
        try {
            String jsonData = objectMapper.writeValueAsString(moviePage);
            redisTemplate.opsForValue().set(cacheKey, jsonData,
                    PAGE_EXPIRE_TIME, TimeUnit.MINUTES);
        } catch (Exception e) {
            log.error("Redis缓存存储失败", e);
        }

        return moviePage;
    }

    // 批量删除电影
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void batchDeleteMovies(List<Long> ids) throws ServiceException {
        if (CollectionUtils.isEmpty(ids)) {
            return;
        }

        try {
            // 删除 Redis 缓存
            List<MovieJson> movies = this.listByIds(ids);
            for (MovieJson movie : movies) {
                if (movie != null && movie.getSlug() != null) {
                    String redisKey = REDIS_KEY_PREFIX + movie.getSlug();
                    redisTemplate.delete(redisKey);
                    log.info("删除Redis缓存: {}", redisKey);
                }
            }

            // 直接删除,让 ShardingSphere 处理分片路由
            boolean success = this.removeByIds(ids);
            log.info("批量删除记录结果: {}", success);

            // 清除分页缓存
            clearPageCache();

            // 验证删除结果
            List<MovieJson> remainingMovies = this.listByIds(ids);
            if (!remainingMovies.isEmpty()) {
                log.error("删除失败，仍有 {} 条记录存在", remainingMovies.size());
                throw new ServiceException("删除失败，部分记录未能删除");
            }
        } catch (Exception e) {
            log.error("批量删除电影失败", e);
            throw new ServiceException("批量删除电影失败: " + e.getMessage());
        }
    }

    // 导出电影数据
    @Override
    public List<MovieDetail> exportMoviesData(List<Long> ids) throws ServiceException {
        try {
            // 获取所有要导出的电影
            List<MovieJson> movies = this.listByIds(ids);
            List<MovieDetail> movieDetails = new ArrayList<>();

            // 转换每个电影的JSON数据为MovieDetail对象
            for (MovieJson movie : movies) {
                try {
                    MovieDetail detail = objectMapper.readValue(
                            movie.getJsonContent(),
                            MovieDetail.class
                    );
                    movieDetails.add(detail);
                } catch (Exception e) {
                    log.error("解析电影数据失败, id: " + movie.getId(), e);
                    // 跳过解析失败的数据，继续处理下一个
                    continue;
                }
            }

            if (movieDetails.isEmpty()) {
                throw new ServiceException("没有可导出的数据");
            }

            return movieDetails;
        } catch (Exception e) {
            log.error("导出电影数据失败", e);
            throw new ServiceException("导出电影数据失败: " + e.getMessage());
        }
    }

    @Override
    // 批量删除电影
    public void deleteMovieById(Long id) throws ServiceException {
        try {
            MovieJson movie = this.getById(id);
            if (movie == null) {
                throw new ServiceException("电影不存在");
            }

            // 删除Redis缓存
            redisTemplate.delete(REDIS_KEY_PREFIX + movie.getSlug());
            // 删除数据库记录
            this.removeById(id);
            // 清除分页缓存
            clearPageCache();
        } catch (Exception e) {
            log.error("删除电影失败", e);
            throw new ServiceException("删除电影失败: " + e.getMessage());
        }
    }

    @Override
    public MovieDetail getMovieDetail(String slug) {
        // 先从Redis获取
        String jsonContent = getFromRedis(slug);
        if (jsonContent == null) {
            // 从数据库获取
            MovieJson entity = this.lambdaQuery()
                    .eq(MovieJson::getSlug, slug)
                    .oneOpt()
                    .orElseThrow(() -> new ServiceException("未找到电影: " + slug));

            jsonContent = entity.getJsonContent();
            // 存入Redis
            saveToRedis(slug, jsonContent);
        }

        // 解析JSON字符串为MovieDetail对象
        try {
            return objectMapper.readValue(jsonContent, MovieDetail.class);
        } catch (Exception e) {
            log.error("解析JSON失败", e);
            throw new ServiceException("数据格式错误");
        }
    }

    @Override
    public String startBatchProcess(MultipartFile file, ContentPosition contentPosition) {
        // 验证文件
        validateExcelFile(file);

        // 生成批次号
        String batchNo = generateBatchNo();

        try {
            // 解析Excel获取总行数
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            int totalRows = sheet.getLastRowNum();

            // 创建批次记录
            MovieUploadBatch batch = new MovieUploadBatch();
            batch.setBatchNo(batchNo);
            batch.setTotalCount(totalRows);
            batch.setStatus("PROCESSING");
            batch.setContentPosition(contentPosition.name());
            batch.setCreateTime(LocalDateTime.now());
            batch.setUpdateTime(LocalDateTime.now());
            uploadBatchMapper.insert(batch);

            // 异步处理
            taskExecutor.execute(() -> processExcelAsync(file, contentPosition, batchNo));

            return batchNo;
        } catch (Exception e) {
            throw new ServiceException("启动批处理失败: " + e.getMessage());
        }
    }

    // 生成批次号
    private String generateBatchNo() {
        return "BATCH" + System.currentTimeMillis() +
                String.format("%04d", new Random().nextInt(10000));
    }

    // 更新批次进度
    private void updateBatchProgress(String batchNo, int successCount, int failCount) {
        uploadBatchMapper.update(null,
                new LambdaUpdateWrapper<MovieUploadBatch>()
                        .eq(MovieUploadBatch::getBatchNo, batchNo)
                        .set(MovieUploadBatch::getSuccessCount, successCount)
                        .set(MovieUploadBatch::getFailCount, failCount)
                        .set(MovieUploadBatch::getUpdateTime, LocalDateTime.now())
        );
    }

    // 完成批次
    private void completeBatch(String batchNo, int successCount, int failCount) {
        uploadBatchMapper.update(null,
                new LambdaUpdateWrapper<MovieUploadBatch>()
                        .eq(MovieUploadBatch::getBatchNo, batchNo)
                        .set(MovieUploadBatch::getSuccessCount, successCount)
                        .set(MovieUploadBatch::getFailCount, failCount)
                        .set(MovieUploadBatch::getStatus, "COMPLETED")
                        .set(MovieUploadBatch::getUpdateTime, LocalDateTime.now())
        );
    }

    // 批次失败
    private void failBatch(String batchNo, String errorMsg) {
        uploadBatchMapper.update(null,
                new LambdaUpdateWrapper<MovieUploadBatch>()
                        .eq(MovieUploadBatch::getBatchNo, batchNo)
                        .set(MovieUploadBatch::getStatus, "FAILED")
                        .set(MovieUploadBatch::getErrorMsg, errorMsg)
                        .set(MovieUploadBatch::getUpdateTime, LocalDateTime.now())
        );
    }

    // 获取批次进度
    public BatchProgressResponse getBatchProgress(String batchNo) {
        MovieUploadBatch batch = uploadBatchMapper.selectOne(
                new LambdaQueryWrapper<MovieUploadBatch>()
                        .eq(MovieUploadBatch::getBatchNo, batchNo)
        );

        if (batch == null) {
            throw new ServiceException("批次不存在");
        }

        BatchProgressResponse response = new BatchProgressResponse();
        response.setTotalCount(batch.getTotalCount());
        response.setSuccessCount(batch.getSuccessCount());
        response.setFailCount(batch.getFailCount());
        response.setStatus(batch.getStatus());
        response.setErrorMsg(batch.getErrorMsg());

        return response;
    }

    // 从Redis获取数据
    private String getFromRedis(String slug) {
        try {
            return redisTemplate.opsForValue().get(REDIS_KEY_PREFIX + slug);
        } catch (Exception e) {
            log.error("从Redis获取数据失败", e);
            return null;
        }
    }

    private String generatePageCacheKey(long current, long size, String slug) {
        return REDIS_PAGE_KEY + current + ":" + size + ":" +
                (slug != null ? slug : "all");
    }

    // 清除分页缓存
    private void clearPageCache() {
        try {
            Set<String> keys = redisTemplate.keys(REDIS_PAGE_KEY + "*");
            if (!keys.isEmpty()) {
                redisTemplate.delete(keys);
            }
        } catch (Exception e) {
            log.error("清除分页缓存失败", e);
        }
    }

    // 批量处理数据
    private void processBatch(List<MovieDTO> items) {
        for (MovieDTO item : items) {
            try {
                String json = jsonGenerator.generateRandomJson(item);
                saveToRedisAndDB(item, json);
            } catch (Exception e) {
                log.error("处理数据失败: {"+e.getMessage()+"}");
                throw e;
            }
        }
    }
}